import pymysql
from utils import get_connection, get_client_ip
from datetime import datetime

def execute_query(query, params=None):
    """执行数据库查询并返回结果"""
    conn = None
    try:
        conn = get_connection()
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(query, params)
            if query.strip().upper().startswith("SELECT"):
                result = cursor.fetchall() if "ALL" in query.upper() else cursor.fetchone()
                return result
            else:
                conn.commit()
                return True  # 对于非查询操作，返回 True 表示操作成功
    except Exception as e:
        print(f"Error: {e}")
        if conn:
            conn.rollback()
        return None
    finally:
        if conn:
            conn.close()


def get_user(username):
    query = "SELECT Username, Password FROM users WHERE username = %s LIMIT 1"
    return execute_query(query, (username))


def get_logging():
    conn = get_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute("SELECT * FROM login_data")
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data


def create_table_login_data():
    query = """  
    CREATE TABLE IF NOT EXISTS `login_data` (  
        `id` INT AUTO_INCREMENT PRIMARY KEY,  
        `username` VARCHAR(255) NOT NULL,  
        `time` DATETIME,
        `ip` VARCHAR(255),
        `status` VARCHAR(255)
    );
    """
    return execute_query(query)


def insert_login_data(username,ip_address,status):
    current_time = datetime.now()
    query = "INSERT INTO `login_data` (`username`, `time`, `ip`, `status`) VALUES (%s, %s, %s, %s)"
    return execute_query(query, (username, current_time, ip_address, status))

def insert_user_data(username, hashed_password, email, phone, role):
    # 注意：hashed_password 应该是已经经过哈希处理的密码
    query = """  
    INSERT INTO `users` (`Username`, `Password`, `Email`, `Phone`, `Role`)  
    VALUES (%s, %s, %s, %s, %s)  
    """
    params = (username, hashed_password.decode('utf-8') if isinstance(hashed_password, bytes) else hashed_password, email, phone, role)
    return execute_query(query, params)


def get_login_data():
    query = "SELECT username, time, ip FROM login_data ORDER BY id DESC LIMIT 1"
    return execute_query(query)


def get_information(username):
    query = "SELECT Id, Username, Email, Phone, Role FROM users WHERE Username = %s LIMIT 1"
    return execute_query(query, username)
